package com.biz.hunter.dao;

import com.biz.hunter.db.entity.City;
import com.biz.hunter.db.entity.MyCalendar;
import com.biz.hunter.dbutil.BaseDAO;
import com.mysql.jdbc.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.Collections;
import java.util.List;
import java.util.Objects;

/**
 * @author jerry
 */
@Repository
public class CalendarDao {
    private static final String SQL_SAVE = "insert into calendar(id,title,starttime,endtime,allday,color,userid,hunterid) values(?,?,?,?,?,?,?,? )";
    private static final String SQL_GET_ID = "select * from calendar  where id = ?";
    private static final String SQL_GET_HUNTERID = "select id,title,starttime as 'start', endtime as 'end',allday,color,userid,hunterid,isbook from calendar  where hunterid = ?";
    private static final String SQL_GET_ALL = "select * from calendar ";
    private static final String SQL_DEL_ID = "delete from calendar where id = ?";
    private static final String SQL_LIST_BY_HUNTERID = "select * from calendar where hunterid = ?";
    private static final String SQL_UPDATE_BY_ID = "update calendar set title=?,starttime=?,endtime=?,allday=?,color=?,userid=?,hunterid=? ,isbook=? where id=?";
    private static final String SQL_GET_COUNT = "select count(1) from calendar where hunterid=? and  NOT ( (endtime < ?) OR (starttime > ?))";

    public List<City> getALL() {

        return BaseDAO.queryForList(City.class, SQL_GET_ALL);

    }

    public MyCalendar getById(String Id) {
        return BaseDAO.queryForObject(MyCalendar.class, SQL_GET_ID, Id);
    }

    public List<MyCalendar> listByHunterId(Integer hunterId) {
        List<MyCalendar> result = BaseDAO.queryForList(MyCalendar.class, SQL_GET_HUNTERID, hunterId);
        return result;
    }


    public Integer save(MyCalendar myCalendar) {
        if (Objects.isNull(myCalendar)) {
            return -1;
        }
        int result = BaseDAO.execute(SQL_SAVE, myCalendar.getId(), myCalendar.getTitle(),
                myCalendar.getStarttime(), myCalendar.getEndtime(), myCalendar.getAllDay(),
                myCalendar.getColor(), myCalendar.getHunterId(), myCalendar.getHunterId());
        System.out.println("[Save MyCalendar  SQL]: " + SQL_SAVE + " rows:" + result);
        return result;
    }

    public Integer deleteById(String id) {
        int result = BaseDAO.execute(SQL_DEL_ID, id);
        return result;
    }

    public List<MyCalendar> getByHunterId(int id) {
        List<MyCalendar> result = BaseDAO.queryForList(MyCalendar.class, SQL_LIST_BY_HUNTERID, id);
        if (result == null) {
            return Collections.emptyList();
        }
        return result;
    }

    public void updateById(MyCalendar myCalendar) {
        BaseDAO.updateone(SQL_UPDATE_BY_ID, myCalendar.getTitle(), myCalendar.getStarttime(),
                myCalendar.getEndtime(), myCalendar.getAllDay(), myCalendar.getColor(),
                myCalendar.getUserid(), myCalendar.getHunterId(), myCalendar.getBook(), myCalendar.getId());
    }

    public int validateMycalendar(MyCalendar myCalendar) {
        String sql = SQL_GET_COUNT;
        if (!StringUtils.isEmptyOrWhitespaceOnly(myCalendar.getId())) {
            sql += " and id != '" + myCalendar.getId() + "'";
        }
        Object result = BaseDAO.queryOneRowOneCol(sql, myCalendar.getHunterId(), myCalendar.getStarttime(), myCalendar.getEndtime());
        return Integer.parseInt(result + "");
    }
}
